package createtables

class Main {

	static database
	static tableName
	static shortTableName
	static n = 0
	static pkCampos
	static nombreCampos
	static tipoCampos
	static flagsCampos
	static input = new File("/Users/tito/Downloads/sss_create-tables-final.sql")
	static output = new File("/tmp/create-tables.sql")
	static enables = new File("/tmp/enables.sql")
	static table2File = new File("/Users/tito/Development/ssalud/dumps/nombreTablas")
	static mapTable2File = [:]
	static usedSequenceName = []
	
	static main(args) {
		
		table2File.eachLine { line ->
			def arrField = line.split(' ')
			
			mapTable2File[arrField[1]] = arrField[0]
		}
		
		output.write("")
		enables.write("")
		
		def controlFile = new File('/tmp/control-files')
		controlFile.mkdir()
		
		input.eachLine { line ->
			
			if (line =~ /create/) {
				create(line)
			}
			else if (line =~ /\);/) {
				endCreate()
			}
			else if (line =~ /primary key/) {
				primaryKey(line)
			}
			else if (line.size() == 0 || line =~ /\\s+/ || line =~ /  \(/) {
			}
			else {
				fieldDef(line)
			}
			
		}
		
	}
	
	static create(line) {
		def matches = line =~ /create table "(.+)"\.(.+)/
				
		matches.each { all, database, tableName ->
			Main.database = database
			Main.tableName = tableName
		}

		pkCampos = []
		nombreCampos = []
		tipoCampos = []
		flagsCampos = []
	}

	static endCreate(line) {		
		tableName = tableName.trim().replaceAll('efectores_agropecuarios_enviados_xdesa', 'efec_agrop_env_xdesa')
		
		//output << "drop table " + tableName.trim() + ";\n\n"
		
		output << "create table " + tableName + "\n"
		output << "(\n"
		
		if (pkCampos == [] && !tipoCampos[0].contains('serial')) {
			if (nombreCampos.contains("id"))
				pkCampos = ['id']
			else
				output << "\tid integer,\n"
		}
		
		for (int i = 0; i < nombreCampos.size(); i++) {
			if (i > 0)
				output << ",\n"
			nombreCampos[i] = nombreCampos[i].trim().replaceAll('^user$', "usuario")
			output << "\t" + nombreCampos[i] + " "
			output << convertirTipo(tipoCampos[i])
		}

		output << "\n);\n\n"

		/*
		 * Crear un indice en el tablespace de indices, este indice va a ser el primary key.
		 */
		
		output << 'create unique index pk_' + tableName
		output << ' on ' + tableName + '('

		if (pkCampos != []) {
			pkCampos.eachWithIndex() { campo, i ->
				if (i > 0)
					output << ', '
				output << campo
			}
		}
		else if (tipoCampos[0].contains('serial')) {
			output << nombreCampos[0]
		}
		else {
			output << 'id'
		}
		
		output << ') tablespace indices parallel 4;\n\n'
		
		/*
		 * Establecer ese indice como primary key.
		 */
		
		output << 'alter table ' + tableName
		output << ' add constraint pk_' + tableName
		output << ' primary key ('

		if (pkCampos != []) {
			pkCampos.eachWithIndex() { campo, i ->
				if (i > 0)
					output << ', '
				output << campo
			}
		}
		else if (tipoCampos[0].contains('serial')) {
			output << nombreCampos[0]
		}
		else {
			output << 'id'
		}

		output << ') using index;\n\n'
		
		/*
		 * Desactivar la validacion de primary key.
		 */
		
		output << 'alter table ' + tableName + ' disable primary key;\n\n'
		
		/*
		 * Se reactiva el primary key.
		 */
		enables << "alter table " + tableName + " enable primary key;\n\n"
		

		/*
		 * create autoincremental id trigger
		 */
		if (pkCampos == []) {
			shortTableName = tableName.trim().substring(0, tableName.trim().size() > 26 ? 26 : tableName.trim().size())

			if (usedSequenceName.contains(shortTableName))
				shortTableName = shortTableName.substring(0, 25) + '1'

			usedSequenceName += shortTableName
			
			if (tipoCampos[0].contains('serial')) {
				//output << "drop sequence " + shortTableName + "_seq;\n\n"
				//output << "drop trigger " + shortTableName + "_trg;\n\n"
				
				output << "create sequence " + shortTableName + "_seq minvalue 1000000000;\n\n"
				output << "create trigger " + shortTableName + "_trg\n"
				output << "before insert on " + tableName.trim() + "\n"
				output << "for each row\n"
				//output << "when (new." + nombreCampos[0].trim() + " is null)\n"
				output << "begin\n"
				
				output << "\t:new." + nombreCampos[0].trim() + " := "
				output << shortTableName + "_seq.nextval;\n"
				/*
				output << "\tselect " + shortTableName + "_seq.nextval\n"
				output << "\tinto :new." + nombreCampos[0].trim() + "\n"
				output << "\tfrom dual;\n"
				*/
				
				output << "end;\n"
				output << "/\n\n"
				
				output << 'alter trigger ' + shortTableName + '_trg disable;\n\n'
				
				/*
				 * Se reactiva el trigger
				 */
				enables << 'alter trigger ' + shortTableName + '_trg enable;\n\n'
			}
			else {
				//output << "drop sequence " + shortTableName + "_seq;\n\n"
				//output << "drop trigger " + shortTableName + "_trg;\n\n"
				
				output << "create sequence " + shortTableName + "_seq;\n\n"
				output << "create trigger " + shortTableName + "_trg\n"
				output << "before insert on " + tableName.trim() + "\n"
				output << "for each row\n"
				output << "begin\n"

				output << "\t:new.id := "
				output << shortTableName + "_seq.nextval;\n"
				/*
				output << "\tselect " + shortTableName + "_seq.nextval\n"
				output << "\tinto :new.id\n"
				output << "\tfrom dual;\n"
				*/
				
				output << "end;\n"
				output << "/\n\n"
				
				output << 'alter trigger ' + shortTableName + '_trg disable;\n\n'
				
				/*
				 * Se reactiva el trigger
				 */
				enables << 'alter trigger ' + shortTableName + '_trg enable;\n\n'
			}
		}

		createControlFile()
	}

	static primaryKey(line) {
		def matches = line =~ /\((.+)\)/

		matches.each { all, campos ->
			pkCampos = campos.split(",")
		}
	}
	
	static fieldDef(line) {
		def matches = line =~ /([^ ]+) +(.+)/
		
		matches.each { all, nombre, tipo ->
			nombreCampos += nombre

			tipoCampos +=
					(tipo.trim().charAt(tipo.trim().size() - 1) == ',' ?
					tipo.trim().substring(0, tipo.trim().size() - 1).trim() :
					tipo.trim())

			if (tipo.contains('datetime')) {
				flagsCampos += 'datetime'
			}
			else {
				flagsCampos += ''
			}
		}
	}
	
	static convertirTipo(tipo) {
		tipo = (tipo =~ /constraint +".+"\.[^ ]+/).replaceAll('')
		tipo = (tipo =~ /not +null +disabled/).replaceAll('')
		tipo = (tipo =~ /not +null +filtering/).replaceAll('')
		tipo = (tipo =~ /year to minute/).replaceAll('')
		
		tipo = (tipo =~ /serial/).replaceAll('integer')
		tipo = (tipo =~ /decimal/).replaceAll('number')
		tipo = (tipo =~ /datetime/).replaceAll('date')
		tipo = (tipo =~ /text/).replaceAll('clob')
		tipo = (tipo =~ /lvarchar/).replaceAll('varchar')
		tipo = (tipo =~ / char/).replaceAll(' varchar')
		tipo = (tipo =~ /^char/).replaceAll('varchar')
		tipo = (tipo =~ /varchar/).replaceAll('varchar2')
		
		def matches = tipo =~ /varchar2\((\d+)\)/

		matches.each { all, tamanio ->
			if (Long.valueOf(tamanio) > 4000L)
				tipo = tipo.replaceAll(/varchar2\(\d+\)/, 'clob')
			else
				tipo = tipo.replaceAll(/varchar2\(\d+\)/, 'varchar2(' + tamanio + ' char)')
		}		

		def matches2 = tipo =~ /varchar2\((\d+),\d+\)/
		
		matches2.each { all, tamanio ->
			tipo = tipo.replaceAll(/varchar2\((\d+),\d+\)/, 'varchar2(' + tamanio + ' char)')
		}
				
		return tipo
	}
	
	static createControlFile() {
		def controlFile = new File('/tmp/control-files/' + mapTable2File[tableName.trim()] + '.ctl')
		
		controlFile.write('')
		controlFile << 'LOAD DATA\n'
		controlFile << "CHARACTERSET UTF8\n"
		controlFile << 'INFILE ' + mapTable2File[tableName.trim()] + '.unl\n'
		controlFile << 'TRUNCATE INTO TABLE ' + tableName.trim() + '\n'
		controlFile << "FIELDS TERMINATED BY '|'\n"
		controlFile << '('

		if (pkCampos == []) {
			if (!tipoCampos[0].contains('serial')) {
				controlFile << 'id expression "' + shortTableName + '_seq.nextval", '
			}
		}

		nombreCampos.eachWithIndex() { campo, i ->
			if (i > 0)
				controlFile << ', '
			controlFile << campo
			if (tipoCampos[i].contains('date')) {
				if (flagsCampos[i].contains('datetime'))
					controlFile << " DATE 'YYYY-MM-DD HH24:MI'"
				else
					controlFile << " DATE 'DD/MM/YYYY'"
			}

			/*			
			if (i == 0) {
				if (pkCampos == []) {
					if (tipoCampos[0].contains('serial')) {
						controlFile << ' expression "' + shortTableName + '_seq.nextval"'
					}
				}
			}
			*/
		}
		
		controlFile << ')'
	}
	
}
